package org.nf.sms.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.nf.sms.dao.StaffDao;
import org.nf.sms.entity.Staff;
import org.nf.sms.util.C3p0Util;

import java.sql.SQLException;
import java.util.List;

/**
 * 员工 DAO 接口的实现类 - 基于原生 DbUtils 实现
 *
 * @Author zqx
 * @Date 2024-03-12
 */
public class StaffDaoImpl4DbUtil implements StaffDao {
    // 第一：实例化 QueryRunner - 操作 SQL 语句
    QueryRunner qr = new QueryRunner(C3p0Util.getDataSource());

    @Override
    public int insert(Staff staff) {
        String sql = "insert into staffs(name,age,phone,sta_pos) values(?,?,?,?)";

        try {
            return qr.update(sql, staff.getName(), staff.getAge(), staff.getPhone(), staff.getStaPos());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int delete(int id) {
        String sql = "delete from staffs where id=?";
        try {
            return qr.update(sql, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(Staff staff) {
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=?,update_time=? where id=?";
        try {
            return qr.update(sql, staff.getName(), staff.getAge(), staff.getPhone(), staff.getStaPos(), staff.getUpdateTime(), staff.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Staff selectById(int id) {
        String sql = "select id,name,age,phone,sta_pos as staPos,add_time as addTime,update_time as updateTime from staffs where id=?";
        try {
            // BeanHandler 是 ResultSetHandler 接口的其中一个实现类，返回实体对象
            return qr.query(sql, new BeanHandler<>(Staff.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Staff> selectByPage(int limit, int count) {
        // 第二：定义要操作数据库的SQL语句
        String sql = "select id,name,age,phone,sta_pos as staPos,add_time as addTime,update_time as updateTime from staffs limit ?,?";

        // 第三：执行 - BeanListHandler 对查询的结果进行自动处理、封装返回一个List<Staff>集合 -- 反射-
        List<Staff> list = null;
        try {
            // ResultSetHandler VS BeanListHandler
            // ResultSetHandler 结果集处理器，通过实现此接口来对结果集数据进行处理
            // BeanListHandler 是 ResultSetHandler 接口的其中一个实现类，返回实体对象的List集合
            Object[] params = {limit, count};

            list = qr.query(sql, new BeanListHandler<Staff>(Staff.class), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public Long selectTotalCount() {
        String sql = "select count(id) from staffs";
        try {
            // ScalarHandler 是 ResultSetHandler 接口的其中一个实现类，返回一行一列的数据
            return qr.query(sql, new ScalarHandler<Long>());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
